﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;

namespace WpfWordsLearner.DAO
{
	[Alias("Words")]
	public class Words
			: ServiceStack.DesignPatterns.Model.IHasId<int>
	{
		[AutoIncrement]
		[Alias("Id")]
		public int Id { get; set; }

		[System.ComponentModel.DataAnnotations.Required]
		[System.ComponentModel.DataAnnotations.StringLength(255)]
		public string Word { get; set; }

		[System.ComponentModel.DataAnnotations.Required]
		[System.ComponentModel.DataAnnotations.StringLength(255)]
		public string Translation { get; set; }

		[ServiceStack.DataAnnotations.Default(0)]
		[System.ComponentModel.DataAnnotations.Required]
		public int Complicated { get; set; }

		[References(typeof(Sound))]
		[Index(Unique = true)]
		public int SoundId { get; set; }
	}

	[Alias("Sounds")]
	public class Sound
			: ServiceStack.DesignPatterns.Model.IHasId<int>
	{
		[AutoIncrement]
		[Alias("Id")]
		public int Id { get; set; }

		[Index(Unique = true)]
		[System.ComponentModel.DataAnnotations.Required]
		public int WordId { get; set; }

		public byte[] Voice { get; set; }
	}

	[Alias("VoicedWords")]
	public class VoicedWord
			: ServiceStack.DesignPatterns.Model.IHasId<int>
	{
		[AutoIncrement]
		[Alias("Id")]
		public int Id { get; set; }

		[System.ComponentModel.DataAnnotations.Required]
		public string Word { get; set; }

		public byte[] Voice { get; set; }
	}

	public sealed class WordsDao
	{
		string m_pathToDB = null;
		public WordsDao(string path)
		{
			if (string.IsNullOrEmpty(path))
				throw new ArgumentNullException("path");

			m_pathToDB = path;
			ServiceStack.OrmLite.OrmLiteConfig.DialectProvider = new ServiceStack.OrmLite.Sqlite.SqliteOrmLiteDialectProvider();
		}

		public void Initialize()
		{
			bool exist = true;
			if (!System.IO.File.Exists(m_pathToDB))
				exist = false;

			//using (System.Data.IDbConnection dbConn = ":memory:".OpenDbConnection())
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				if (!exist)
				{
					dbCmd.CreateTables(false, typeof(Words), typeof(Sound), typeof(VoicedWord));
				}
				else
				{
					dbCmd.DeleteAll(typeof(VoicedWord));
					dbCmd.DeleteAll(typeof(Words));
					dbCmd.DeleteAll(typeof(Sound));
				}

				dbCmd.Insert(new Words { Word = "A", Translation = "a", SoundId = 1 });
				dbCmd.Insert(new Words { Word = "B", Translation = "b", SoundId = 2 });

				var rowsB = dbCmd.Select<Words>("Word = {0}", "A");
				var rowsB1 = dbCmd.Select<Words>(word => word.Word == "B");
				rowsB.ForEach(w => Console.WriteLine(w.Translation));
				rowsB1.ForEach(w => Console.WriteLine(w.Translation));
				rowsB.ForEach(x => dbCmd.Delete(x));
				rowsB1.ForEach(x => dbCmd.Delete(x));
			}
		}

		public bool ContainsWord(string searchWord)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var rows = dbCmd.Select<Words>("Word={0}", searchWord);
				return rows.Count > 0;
			}
		}

		public bool ContainsWord(int id)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var rows = dbCmd.Select<Words>(word => word.Id == id);
				return rows.Count > 0;
			}
		}

		public void CreateWord(Words word)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				dbCmd.Insert<Words>(word);
				var searched = dbCmd.FirstOrDefault<Words>("Word={0}", word.Word);
				if (searched != null)
				{
					Sound voice = new Sound();
					voice.WordId = searched.Id;
					dbCmd.Insert<Sound>(voice);

					var srchVoice = dbCmd.FirstOrDefault<Sound>(v => v.WordId == searched.Id);
					if (srchVoice != null)
					{
						srchVoice.WordId = searched.Id;
						dbCmd.Update<Sound>(srchVoice);
					}
				}
			}
		}

		public void CreateVoice(Sound sound)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				dbCmd.Insert<Sound>(sound);
			}
		}

		public bool IsVoiced(string word)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var wor = dbCmd.FirstOrDefault<Words>("Word={0}", word);
				if (wor != null)
				{
					var voice = dbCmd.FirstOrDefault<Sound>(v => v.WordId == wor.Id);
					return (voice != null && voice.Voice != null);
				}
				return false;
			}
		}

		public void AddVoiceForWord(string word, byte[] voice)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				Words searched = dbCmd.FirstOrDefault<Words>(x => string.Compare(x.Word, word, true) == 0);
				if (searched != null)
				{
					Sound sound = dbCmd.FirstOrDefault<Sound>(v => v.Id == searched.SoundId);
					if (sound != null && sound.Voice == null)
					{ }
				}
			}
		}

		#region VoicedWords
		public void AddVoicedWord(string word, byte[] data)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var serached = dbCmd.FirstOrDefault<DAO.VoicedWord>("Word={0}", word);
				if (serached == null)
				{
					VoicedWord vw = new VoicedWord();
					vw.Word = word;
					vw.Voice = data;
					dbCmd.Insert<VoicedWord>(vw);
				}
				else if (serached != null && serached.Voice == null)
				{
					serached.Voice = data;
					dbCmd.Update<VoicedWord>(serached);
				}
			}
		}

		public bool IsWordVoiced(string word)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var serached = dbCmd.FirstOrDefault<DAO.VoicedWord>("Word={0}", word);
				return (serached != null && serached.Voice != null);
			}
		}

		public VoicedWord GetVoice(string word)
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var serached = dbCmd.FirstOrDefault<DAO.VoicedWord>("Word={0}", word);
				return serached;
			}
		}

		public List<byte[]> GetVoices()
		{
			using (System.Data.IDbConnection dbConn = m_pathToDB.OpenDbConnection())
			using (System.Data.IDbCommand dbCmd = dbConn.CreateCommand())
			{
				var serached = dbCmd.Select<DAO.VoicedWord>(x => x.Id > 0);
				return serached.Select(x => x.Voice).ToList();
			}
		}

		#endregion
	}

	public sealed class WordsDaoPlain : IDisposable
	{
		private const string с_DbConString = "Data Source={0};Version=3;Pooling=False;Max Pool Size=100;";
		private System.Data.SQLite.SQLiteConnection m_con = null;

		public WordsDaoPlain(string pathToDb)
		{
			m_con = new System.Data.SQLite.SQLiteConnection(string.Format(с_DbConString, pathToDb));
		}

		private void CloseConnection()
		{
			if (m_con.State == System.Data.ConnectionState.Open)
				m_con.Close();
		}

		private void OpenConnection()
		{
			if (m_con.State == System.Data.ConnectionState.Closed)
				m_con.Open();
		}

		public List<byte[]> GetVoices()
		{
			OpenConnection();

			System.Data.SQLite.SQLiteCommand command = m_con.CreateCommand();
			command.CommandText = "SELECT Voice FROM VoicedWords";
			var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

			List<byte[]> data = new List<byte[]>();
			while (reader.Read())
			{
				byte[] buffer = GetBytes(reader);
				data.Add(buffer);
			}
			return data;
		}

		public void AddVoicedWord(string word, byte[] data)
		{
			OpenConnection();

			System.Data.SQLite.SQLiteCommand command = m_con.CreateCommand();
			command.CommandText = "INSERT INTO VoicedWords(Id,Word,Voice) VALUES(NULL,@p1,@p2)";

			var p1 = new System.Data.SQLite.SQLiteParameter(System.Data.DbType.String);
			p1.Value = word;
			p1.ParameterName = "p1";
			command.Parameters.Add(p1);

			var p2 = new System.Data.SQLite.SQLiteParameter(System.Data.DbType.Binary);
			p2.Value = data;
			p2.ParameterName = "p2";
			command.Parameters.Add(p2);

			command.ExecuteNonQuery();
			command.Dispose();

			CloseConnection();
		}

		public void ClearVoice()
		{
			OpenConnection();

			System.Data.SQLite.SQLiteCommand command = m_con.CreateCommand();
			command.CommandText = "DELETE FROM VoicedWords WHERE Id>0";

			command.ExecuteNonQuery();
			command.Dispose();

			CloseConnection();
		}

		public bool IsWordVoiced(string word)
		{
			OpenConnection();

			System.Data.SQLite.SQLiteCommand command = m_con.CreateCommand();
			command.CommandText = "SELECT COUNT(Id) FROM VoicedWords WHERE Word=@p1";

			var p1 = new System.Data.SQLite.SQLiteParameter(System.Data.DbType.String);
			p1.Value = word;
			p1.ParameterName = "p1";
			command.Parameters.Add(p1);

			object result = command.ExecuteScalar();
			command.Dispose();

			CloseConnection();

			if (result != null)
			{
				int count = Convert.ToInt32(result);
				return count > 0;
			}
			return false;
		}

		public byte[] GetVoiceForWord(string word)
		{
			OpenConnection();

			System.Data.SQLite.SQLiteCommand command = m_con.CreateCommand();
			command.CommandText = "SELECT Voice FROM VoicedWords WHERE Word=@p1";

			var p1 = new System.Data.SQLite.SQLiteParameter(System.Data.DbType.String);
			p1.Value = word;
			p1.ParameterName = "p1";
			command.Parameters.Add(p1);

			var reader = command.ExecuteReader();
			byte[] data = null;
			while (reader.Read())
			{
				data = GetBytes(reader);
				break;
			}
			command.Dispose();

			return data;
		}

		private byte[] GetBytes(System.Data.SQLite.SQLiteDataReader reader)
		{
			const int CHUNK_SIZE = 2 * 1024;
			byte[] buffer = new byte[CHUNK_SIZE];
			long bytesRead;
			long fieldOffset = 0;
			using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
			{
				while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
				{
					byte[] actualRead = new byte[bytesRead];
					Buffer.BlockCopy(buffer, 0, actualRead, 0, (int)bytesRead);
					stream.Write(actualRead, 0, actualRead.Length);
					fieldOffset += bytesRead;
				}
				return stream.ToArray();
			}
		}

		public void Dispose()
		{
			if (m_con != null)
			{
				if (m_con.State == System.Data.ConnectionState.Open)
					m_con.Close();
				m_con.Dispose();
				m_con = null;
			}
		}
	}
}
